/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package model;
import GUI.other.LoadingForm;
import functions.Dogs;
import functions.Dogy;
import java.awt.Dialog;
import java.awt.Dialog.ModalityType;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JDialog;
import javax.swing.JOptionPane;
import javax.swing.SwingUtilities;


/**
 *
 * @author JArik
 */
public class Database {
    private static Connection con;
    private static Statement stat;
    private  String dbName;
    private  String userName;
    private  String password;
    
    public Database (String db, String username, String pass) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException
    {
        dbName = db;
        userName = username;
        password = pass;
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        con = null;
        con = DriverManager.getConnection("jdbc:mysql://"+Dogy.DB_ADDRESS+"/"+db+"?zeroDateTimeBehavior=convertToNull",
        username, pass);
        
    }

    
    public Database()
    {
        
    }

    public static void connect(String db, String username, String pass)throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException
    {
        //dbName = db;
        //userName = username;
        //password = pass;
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        con = null;
        con = DriverManager.getConnection("jdbc:mysql://"+Dogy.DB_ADDRESS+"/"+db,
        username, pass);
        Dogs.prepareHealingStatements(con);
        //updateDB();
    }
    
    public static void updateDB()
    {

    }
    
    public static void beginTransaction() throws SQLException
    {
        con.setAutoCommit(false);
    }
    public static void enableAutoCommit() throws SQLException
    {
        con.setAutoCommit(true);
    }
    public static void endTransaction() throws SQLException
    {
        con.commit();
    }
    public static void rollBack() throws SQLException
    {
        con.rollback();
    }
    
    public ResultSet getDBInfo(String what, String where, String condition) throws SQLException
    {
        closeStatement();
        ResultSet rs = null;
        if(stat != null)
            stat = null;
        if(con == null ){ throw new SQLException("Нет соединения с базой данных или она выключена."); }
        stat = con.createStatement();
        if(stat==null) { throw new SQLException("Обращение к базе даннах невозможно."); }
        //else
        stat.setFetchSize(Integer.MIN_VALUE);

        if (condition.equals(""))
            rs = stat.executeQuery("SELECT "+what+" FROM "+where);
        else
            rs = stat.executeQuery("SELECT "+what+" FROM "+where + " WHERE " + condition);
        //stat.close();
       
        return rs;
    }
    public ResultSet getDBInfo_thr(final String what, final String where, final String condition)
            throws SQLException
    {
      final HashMap threadReturn = new HashMap();
      Thread run = new Thread()
      {
       public void run()
       {
           try
           {
            System.out.println("Start ");
            ResultSet rs = null;
            if(stat != null)
                stat = null;
            if(con == null ){ throw new SQLException("Нет соединения с базой данных или она выключена."); }
            stat = con.createStatement();
            if(stat==null) { throw new SQLException("Обращение к базе даннах невозможно."); }
            //else
            if (condition.equals(""))
                rs = stat.executeQuery("SELECT "+what+" FROM "+where);
            else
                rs = stat.executeQuery("SELECT "+what+" FROM "+where + " WHERE " + condition);
            threadReturn.put("Result",rs);
            System.out.println("Done ");
           }catch(Throwable t)
           {
               threadReturn.put("Exception",t);
           }
       }
      };
      SwingUtilities.invokeLater(run);
      System.out.println("loading");

      while(threadReturn.size() == 0)
      {
          //System.out.println("loading");
            //try {
                //Thread.sleep(100);
            //} catch (InterruptedException ex) { }
      }
      if(threadReturn.get("Exception") != null)
          throw new SQLException((Throwable)threadReturn.get("Exception"));
      else
        return (ResultSet)threadReturn.get("Result");
    }

    
    public int updateDBInfo(String where, String newVal, String condition) throws SQLException
    {
        closeStatement();
        int ret;
        if(stat != null)
            stat = null;
        if(con == null ){ throw new SQLException("Нет соединения с базой данных или она выключена."); }
        stat = con.createStatement();
        if (stat==null ) { throw new SQLException("Обращение к базе даннах невозможно."); }
        //else
        ret = stat.executeUpdate("UPDATE "+where +" SET "+newVal +" WHERE "+condition);
        stat.close();
        return ret;
    }

    public int updateDBInfo(String where, HashMap vars, String condition) throws SQLException
    {
        closeStatement();
        int ret;
        if(stat != null)
            stat = null;
        if(con == null ){ throw new SQLException("Нет соединения с базой данных или она выключена."); }
        stat = con.createStatement();
        if (stat==null ) { throw new SQLException("Обращение к базе даннах невозможно."); }
        //else
        String newVal=" ";
        Iterator keys = vars.keySet().iterator();
        while(keys.hasNext())
        {
            Object key = keys.next();
            if(!newVal.trim().isEmpty())
                newVal +=", ";
            newVal += key+"="+vars.get(key);
        }
        ret = stat.executeUpdate("UPDATE "+where +" SET "+newVal +" WHERE "+condition);
        stat.close();
        return ret;
    }
    public ResultSet insertDBInfo(String where, HashMap vars) throws SQLException
    {
        closeStatement();
        if(stat != null)
            stat = null;
        if(con == null ){ throw new SQLException("Нет соединения с базой данных или она выключена."); }
        stat = con.createStatement();
        if (stat==null) { throw new SQLException("Обращение к базе даннах невозможно."); }
        //else
        String what="";
        String values="";
        Iterator keys = vars.keySet().iterator();
        while(keys.hasNext())
        {
            Object key = keys.next();
            if(!what.trim().isEmpty())
            {
                what +=", ";
                values +=", ";
            }
            what += key;
            values += vars.get(key);
        }
        stat.executeUpdate("INSERT INTO "+where +" ("+ what + ") "+
                                                     " VALUES ("+values+")",Statement.RETURN_GENERATED_KEYS);
        ResultSet rs = stat.getGeneratedKeys();
        //stat.close();
        return rs;
    }
    public ResultSet insertDBInfo(String where, String what, String values) throws SQLException
    {
        closeStatement();
        int ret;
        if(stat != null)
            stat = null;
        if(con == null ){ throw new SQLException("Нет соединения с базой данных или она выключена."); }
        stat = con.createStatement();
        if (stat==null) { throw new SQLException("Обращение к базе даннах невозможно."); }
        //else
        if(what.equals(""))
          ret = stat.executeUpdate("INSERT INTO "+where +" VALUES ("+values+")",Statement.RETURN_GENERATED_KEYS);
        else
          ret = stat.executeUpdate("INSERT INTO "+where +" ("+ what + ") "+
                                                     " VALUES ("+values+")",Statement.RETURN_GENERATED_KEYS);
        ResultSet rs = stat.getGeneratedKeys();
        //stat.close();
        return rs;
    }
    public ResultSet insertIgnoreDBInfo(String where, String what, String values) throws SQLException
    {
        closeStatement();
        int ret;
        if(stat != null)
            stat = null;
        if(con == null ){ throw new SQLException("Нет соединения с базой данных или она выключена."); }
        stat = con.createStatement();
        if (stat==null) { throw new SQLException("Обращение к базе даннах невозможно."); }
        //else
        if(what.equals(""))
          ret = stat.executeUpdate("INSERT IGNORE INTO "+where +" VALUES ("+values+")",Statement.RETURN_GENERATED_KEYS);
        else
          ret = stat.executeUpdate("INSERT IGNORE INTO "+where +" ("+ what + ") "+
                                                     " VALUES ("+values+")",Statement.RETURN_GENERATED_KEYS);
        ResultSet rs = stat.getGeneratedKeys();
        //stat.close();
        return rs;
    }
    public int deleteDBInfo(String where, String condition) throws SQLException
    {
        closeStatement();
        int ret;
        if(stat != null)
            stat = null;
        if(con == null ){ throw new SQLException("Нет соединения с базой данных или она выключена."); }
        stat = con.createStatement();
        if (stat==null) { throw new SQLException("Обращение к базе даннах невозможно."); }
       // else
        ret = stat.executeUpdate("DELETE FROM "+where+" WHERE "+condition);
        stat.close();
        return ret;
    }

    public void closeStatement() throws SQLException
    {
        if(stat !=null)
        stat.close();
    }
    public ResultSet getDBInfo(String query) throws SQLException
    {
        ResultSet rs = null;
        closeStatement();
        
        if(con == null ){ throw new SQLException("Нет соединения с базой данных или она выключена."); }
        stat = con.createStatement();
        if(stat==null) { throw new SQLException("Обращение к базе даннах невозможно."); }
        stat.setFetchSize(Integer.MIN_VALUE);
        rs = stat.executeQuery(query);

        //stat.close();
        return rs;
    }
    public static void cancelQuery() throws SQLException
    {
        stat.cancel();
    }
    public ResultSet insertDBInfo(String query) throws SQLException
    {
        closeStatement();
        int ret;
        if(con == null ){ throw new SQLException("Нет соединения с базой данных или она выключена."); }
        stat = con.createStatement();

        if (stat==null) { throw new SQLException("Обращение к базе даннах невозможно."); }
          ret = stat.executeUpdate(query,Statement.RETURN_GENERATED_KEYS);

        ResultSet rs = stat.getGeneratedKeys();
        //stat.close();
        return rs;
    }
    public int updateDBInfo(String query) throws SQLException
    {
        closeStatement();
        int ret;
        if(con == null ){ throw new SQLException("Нет соединения с базой данных или она выключена."); }
        stat = con.createStatement();
        if (stat==null ) { throw new SQLException("Обращение к базе даннах невозможно."); }
        ret = stat.executeUpdate(query);
        stat.close();

        return ret;
    }

    /*public static ResultSet getFromMergedTables() throws SQLException
    {
        stat = con.createStatement();
        if (stat==null) { return null; }
        else
        return stat.executeQuery("SELECT O.id, O.distrId, O.name, O.descr, O.date, profiles.id, profiles.name " +
                                 "FROM (SELECT O.id, O.distrId, O.name, OM.exec, OM.descr, O.expDate, OM.date FROM drugs O RIGHT JOIN discharged_drugs OM ON O.id = OM.drugId WHERE OM.dogId = 1 ORDER BY OM.date) O"+
                                 "LEFT JOIN profiles ON profiles.id = O.exec");
    }*/
    
    public static void closeConnection() throws SQLException
    {
        if(con == null ){ throw new SQLException("Нет соединения с базой данных или она выключена."); }
        con.close();
    }

}
